package crud.service.impl;

//JDK库

import java.sql.Connection;
import java.util.*;

//工具库
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.lang.tree.Tree;
import cn.hutool.core.lang.tree.TreeNode;
import cn.hutool.core.lang.tree.TreeNodeConfig;
import cn.hutool.core.lang.tree.TreeUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

//框架库
import crud.Application;
import crud.domain.CrudResult;
import crud.domain.SQLExecuteParam;
import crud.domain.SysInterface;
import crud.verify.ParamVerify;
import org.springframework.stereotype.Service;

import lombok.extern.slf4j.Slf4j;
//内部包
import crud.util.SqlParse;
import crud.util.SqlUtil;
import crud.service.SQLCrudService;
import crud.template.SQLTemplate;

import javax.sql.DataSource;

@Service
@Slf4j
public class SQLCrudServiceImpl implements SQLCrudService {

    private synchronized Connection getDbCon(String databaseId) throws Exception {

        DataSource ds = Application.getApplicationContext().getBean(DataSource.class);
        Connection dbCon = ds.getConnection();

        if (dbCon == null) {
            throw new Exception(String.format("数据库连接失败[%s]", ds));
        }

        return dbCon;
    }


    private String executeInsert(String databaseId, String sql, List<Object> paramList) throws Exception {

        Connection dbCon = getDbCon(databaseId);

        //重复记录检查

        String id;
        try {
            id = SqlUtil.executeAdd(dbCon, sql, paramList);
        } finally {
            try {
                dbCon.close();
            } catch (Exception ex) {
                log.error(ex.getMessage());
            }
        }

        return id;
    }

    private boolean executeUpdate(String databaseId, String sql, List<Object> paramList) throws Exception {
        Connection dbCon = getDbCon(databaseId);

        int count;

        try {
            count = SqlUtil.executeUpdate(dbCon, sql, paramList);
        } finally {
            try {
                dbCon.close();
            } catch (Exception ex) {
                log.error(ex.getMessage());
            }
        }

        return count > 0;
    }

    private JSONArray executeQuery(String databaseId, String sql, List<Object> paramList) throws Exception {
        Connection dbCon = getDbCon(databaseId);

        JSONArray rows;
        try {
            rows = SqlUtil.executeQuery(dbCon, sql, paramList);
        } finally {
            try {
                dbCon.close();
            } catch (Exception ex) {
                log.error(ex.getMessage());
            }
        }

        return rows;
    }


    private CrudResult doInsert(SQLExecuteParam param, SysInterface apiConfig, Collection<String> incPermission, String id) throws Exception {
        if (!incPermission.contains("INSERT")) {
            throw new Exception("未解析到[INSERT]权限");
        }

        CrudResult res = new CrudResult();
        try {
            String retId = executeInsert(apiConfig.getDatabaseId(), param.getSql(), param.getParam());
            if (retId == null) {
                retId = id;
            }
            JSONObject data = new JSONObject();
            data.put("id", retId);
            res.setMessage("添加成功");
            res.setStatus(0);
            res.setData(data);

        } catch (Exception ex) {
            log.error(ex.getMessage());
            res.setMessage(String.format("添加失败[%s]", ex.getMessage()));
            res.setStatus(1);
        }
        return res;
    }

    private CrudResult doDelete(SQLExecuteParam param, SysInterface apiConfig, Collection<String> incPermission) throws Exception {
        if (!incPermission.contains("DELETE")) {
            throw new Exception("未解析到[DELETE]权限");
        }

        CrudResult res = new CrudResult();
        boolean ret = executeUpdate(apiConfig.getDatabaseId(), param.getSql(), param.getParam());
        if (ret) {
            res.setStatus(0);
            res.setMessage("删除成功");
        } else {
            res.setStatus(1);
            res.setMessage("删除失败");
        }
        return res;
    }

    private CrudResult doEdit(SQLExecuteParam param, SysInterface apiConfig, Collection<String> incPermission) throws Exception {
        if (!incPermission.contains("UPDATE")) {
            throw new Exception("未解析到[UPDATE]权限");
        }

        CrudResult res = new CrudResult();
        boolean ret = executeUpdate(apiConfig.getDatabaseId(), param.getSql(), param.getParam());
        if (ret) {
            res.setStatus(0);
            res.setMessage("编辑成功");
        } else {
            res.setStatus(1);
            res.setMessage("编辑失败");
        }
        return res;
    }

    private CrudResult doGet(JSONObject reqBody, SQLExecuteParam param, SysInterface apiConfig) throws Exception {

        int page = 1;
        int perPage = 10;

        if (reqBody.containsKey("page")) {
            page = reqBody.getIntValue("page");
            if (page <= 0) {
                page = 1;
            }
        }
        if (reqBody.containsKey("perPage")) {
            perPage = reqBody.getIntValue("perPage");
        }

        if (reqBody.containsKey("orderBy")) {
            String orderBy = reqBody.getString("orderBy");
            boolean ret = orderBy.matches("[A-Za-z0-9_]{1,256}");
            if (!ret) {
                throw new Exception(String.format("orderBy字段值校验未通过[%s]", orderBy));
            }
        }

        if (reqBody.containsKey("orderDir") && reqBody.getString("orderDir") != null) {
            String orderDir = reqBody.getString("orderDir").toLowerCase();
            if (!orderDir.equals("desc") && !orderDir.equals("asc")) {
                reqBody.put("orderDir", "asc");
            }
        }

        CrudResult res = new CrudResult();
        JSONObject data = new JSONObject();

        res.setStatus(0);
        res.setMessage("success");
        data.put("page", page);
        data.put("total", 0);
        data.put("rows", new JSONArray());
        res.setData(data);
        //查询记录总数
        String totalSql = String.format("select count(*) as count from (%s)T", param.getSql());
        log.info("查询总记录数SQL语句[{}],SQL参数{}", totalSql.replace("\n", " ").replaceAll(" +", " "), param.getParam().toString());

        JSONArray rows = executeQuery(apiConfig.getDatabaseId(), totalSql, param.getParam());
        long total = rows.getJSONObject(0).getLong("count");
        if (total == 0) {
            return res;
        }

        data.put("total", total);

        String limitSql = SqlUtil.getLimitString(page, perPage, apiConfig.getDatabaseSqlMode());

        String pageSql = String.format("select * from (%s)T %s", param.getSql(), limitSql);
        log.info("分页查询SQL语句[{}],SQL参数{}", pageSql.replace("\n", " ").replaceAll(" +", " "), param.getParam().toString());
        int index = (page - 1) * perPage;
        rows = executeQuery(apiConfig.getDatabaseId(), pageSql, param.getParam());
        for (Object obj : rows) {
            JSONObject row = (JSONObject) obj;
            row.put("_index", index + 1);
            index += 1;
        }

        data.put("rows", rows);

        return res;
    }

    private CrudResult doRow(JSONObject reqBody, SQLExecuteParam param, SysInterface apiConfig) throws Exception {
        CrudResult res = new CrudResult();
        CrudResult midRes = doGet(reqBody, param, apiConfig);
        JSONObject data = (JSONObject) midRes.getData();
        long total = data.getLong("total");
        if (total == 0) {
            res.setStatus(1);
            res.setMessage("未找到记录");
            res.setData(null);
        } else {
            res.setStatus(0);
            res.setMessage("success");
            res.setData(data.getJSONArray("rows").get(0));
        }
        return res;
    }

    private CrudResult doSelect(JSONObject reqBody, SQLExecuteParam param, SysInterface apiConfig) throws Exception {
        CrudResult res = new CrudResult();
        CrudResult midRes = doGet(reqBody, param, apiConfig);

        res.setStatus(midRes.getStatus());
        res.setMessage(midRes.getMessage());
        res.setData(((JSONObject) midRes.getData()).getJSONArray("rows"));
        return res;
    }


    private CrudResult doTree(JSONObject reqBody, SQLExecuteParam param, SysInterface apiConfig) throws Exception {

        CrudResult midRes = doGet(reqBody, param, apiConfig);

        JSONArray rows = ((JSONObject) midRes.getData()).getJSONArray("rows");

        List<TreeNode<String>> nodeList = CollUtil.newArrayList();
        for (int index = 0; index < rows.size(); index++) {
            JSONObject row = rows.getJSONObject(index);

            String id = row.getString("id");
            String parentId = row.getString("parentid");
            String name = row.getString("label");
            String weight = row.getString("weight");
            nodeList.add(new TreeNode<>(id, parentId, name, weight));
        }
        TreeNodeConfig config = new TreeNodeConfig();
        config.setNameKey("label");
        config.setIdKey("id");
        config.setChildrenKey("children");

        List<Tree<String>> treeList = TreeUtil.build(nodeList, "0", config, (treeNode, tree) -> {
            tree.setId(treeNode.getId());
            tree.setName(treeNode.getName());
            tree.setParentId(treeNode.getParentId());
            tree.setWeight(treeNode.getWeight());
        });


        CrudResult res = new CrudResult();
        res.setStatus(midRes.getStatus());
        res.setMessage(midRes.getMessage());
        String strTreeList = JSON.toJSONString(treeList);

        res.setData(JSON.parseArray(strTreeList));

        return res;
    }


    public void doInsertCheck(JSONObject reqBody, SysInterface apiConfig) throws Exception {
        //校验

        if (apiConfig.getPrimaryFields() == null)
            return;

        String[] primaryFields = apiConfig.getPrimaryFields().split(",");
        List<Object> params = new ArrayList<>();
        String sql = "";
        if (Objects.equals(apiConfig.getDatabaseSqlMode(), "SQLSERVER")) {
            sql = String.format("select count(*) from %s..%s ", apiConfig.getDatabaseSchema(), apiConfig.getTableName());
        } else {
            sql = String.format("select count(*) from %s.%s ", apiConfig.getDatabaseSchema(), apiConfig.getTableName());
        }

        StringBuilder whereSql = new StringBuilder();
        for (String fieldName : primaryFields) {
            if (reqBody.containsKey(fieldName) && reqBody.getString(fieldName) == null) {
                whereSql.append(String.format(" %s = ? and", fieldName));
                params.add(reqBody.getString(fieldName));
            }
        }

        if (whereSql.length() == 0) {
            return;
        }

        sql = String.format("%s where %s", sql, StrUtil.removeSuffix(whereSql.toString(), "and"));
        JSONArray rows = executeQuery(apiConfig.getDatabaseId(), sql, params);
        if (!rows.isEmpty()) {
            throw new Exception(String.format("重复记录[%s]", sql));
        }
    }

    public void doEditCheck(JSONObject reqBody, SysInterface apiConfig) throws Exception {
        //校验

        if (apiConfig.getPrimaryFields() == null)
            return;

        String[] primaryFields = apiConfig.getPrimaryFields().split(",");
        List<Object> params = new ArrayList<>();
        String sql = "";
        if (Objects.equals(apiConfig.getDatabaseSqlMode(), "SQLSERVER")) {
            sql = String.format("select count(*) from %s..%s ", apiConfig.getDatabaseSchema(), apiConfig.getTableName());
        } else {
            sql = String.format("select count(*) from %s.%s ", apiConfig.getDatabaseSchema(), apiConfig.getTableName());
        }

        StringBuilder whereSql = new StringBuilder();
        whereSql.append(String.format(" %s != ? and", apiConfig.getIdField()));
        params.add(reqBody.getString(apiConfig.getIdField()));

        for (String fieldName : primaryFields) {
            if (reqBody.containsKey(fieldName) && reqBody.getString(fieldName) == null) {
                whereSql.append(String.format(" %s = ? and", fieldName));
                params.add(reqBody.getString(fieldName));
            }
        }

        if (whereSql.length() == 0) {
            return;
        }

        sql = String.format("%s where %s", sql, StrUtil.removeSuffix(whereSql.toString(), "and"));
        JSONArray rows = executeQuery(apiConfig.getDatabaseId(), sql, params);
        if (!rows.isEmpty()) {
            throw new Exception(String.format("重复记录[%s]", sql));
        }
    }

    //执行CRUD操作
    @Override
    public CrudResult doCrud(JSONObject reqBody, SysInterface apiConfig) throws Exception {
        String sqlTemplate = apiConfig.getApiTemplate();
        //处理like标签
        String apiActionType = apiConfig.getApiActionType();

        if (Arrays.asList("GET", "LIST", "SELECT", "TREE").contains(apiActionType) && apiConfig.getLikeFields() != null) {
            String[] likeKeyList = apiConfig.getLikeFields().split(",");
            for (String likeKey : likeKeyList) {
                if (reqBody.containsKey(likeKey)) {
                    String likeKeyValue = reqBody.getString(likeKey);
                    if (likeKeyValue != null && !likeKeyValue.isEmpty()) {
                        reqBody.put(likeKey, "%" + likeKeyValue + "%");
                    }
                }
            }
        }

        SQLExecuteParam param = SQLTemplate.buildTemplate(sqlTemplate, reqBody);

        if (Objects.equals(apiActionType, "ADD")) {
            String insertSql = param.getSql();
            insertSql = insertSql.replace(", )", " )");
            param.setSql(insertSql);
        }

        String formatSql = param.getSql().replace("\n", " ").replaceAll(" +", " ");
        log.info("SQL语句[{}],SQL参数{}", formatSql, param.getParam().toString());

        List<SqlParse.SqlStructure> sqlParseList = SqlParse.doParse(apiConfig.getDatabaseSchema(), apiConfig.getDatabaseSqlMode(), param.getSql());
        if (sqlParseList.size() != 1) {
            throw new Exception(String.format("SQL解析失败或包含多条SQL[%s]", param.getSql()));
        }

        SqlParse.SqlStructure sqlStructure = sqlParseList.get(0);
        Collection<String> incPermission = sqlStructure.getIncludePermission();

        for (String pre : new String[]{"DROP", "MERGE", "CREATE", "ALTER", "CREATEINDEX", "DROPINDEX", "REFERENCED"}) {
            if (incPermission.contains(pre)) {
                throw new Exception(String.format("不支持[%s]权限[%s]", pre, param.getSql()));
            }
        }

        CrudResult res;

        if (Objects.equals(apiActionType, "ADD")) {
            //检查必填项
            String requiredFields = apiConfig.getRequiredFields();
            if (requiredFields != null) {
                ParamVerify.requiredFieldInsertCheck(reqBody, requiredFields.split(","));
            }
            //检查重复项
            doInsertCheck(reqBody, apiConfig);
            String id = reqBody.getString(apiConfig.getIdField());
            res = doInsert(param, apiConfig, incPermission, id);
        } else if (Objects.equals(apiActionType, "DELETE")) {
            res = doDelete(param, apiConfig, incPermission);
        } else if (Objects.equals(apiActionType, "EDIT")) {
            //检查必填项 编辑模式，必填项不能为空
            String requiredFields = apiConfig.getRequiredFields();
            if (requiredFields != null) {
                ParamVerify.requiredFieldEditCheck(reqBody, requiredFields.split(","));
            }
            //检查重复项
            doEditCheck(reqBody, apiConfig);
            res = doEdit(param, apiConfig, incPermission);
        } else {
            if (!incPermission.contains("SELECT")) {
                throw new Exception("查询操作解析到[SELECT]关键字");
            }

            for (String pre : new String[]{"INSERT", "DELETE", "UPDATE"}) {
                if (incPermission.contains(pre)) {
                    throw new Exception(String.format("查询操作解析到[%s]关键字,[%s]", pre, param.getSql()));
                }
            }

            if (Objects.equals(apiActionType, "GET")) {
                res = doGet(reqBody, param, apiConfig);
            } else if (Objects.equals(apiActionType, "LIST")) {
                res = doGet(reqBody, param, apiConfig);
            } else if (Objects.equals(apiActionType, "ROW")) {
                res = doRow(reqBody, param, apiConfig);
            } else if (Objects.equals(apiActionType, "SELECT")) {
                res = doSelect(reqBody, param, apiConfig);
            } else if (Objects.equals(apiActionType, "TREE")) {
                res = doTree(reqBody, param, apiConfig);
            } else {
                throw new Exception(String.format("未支持操作类型[SQL][%s]", apiActionType));
            }
        }

        return res;
    }

}